PostgreSQL with SSL

--0016e6db2b1c15e29804840cc0e6
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I'm new on using SSL in PostgreSQL.

I've created a self-signed certificate using openssl and started the server
with ssl suport.

But I have some question, can you help me?

- There is any parameter to configure the path (and name) to certificate
(server.crt) and private key (server.key) like hba_file or ident_file in
postgresql.conf or any other?

- Is it possible to store the server.key in a ciphered file with triple-des
and configure the PostgreSQL to use a simetric-key to open it when it's
necessary?
Maybe I'm wrong but my server only works with I plain private key.

I'm trying to use the java keytool in place of openssl.
- I believe that it not possible to start the PostgreSQL server without
openssl (and ssl-dev package in debian), is it correct?

- When I create keys and certificates with keytool, it creates a java
keystore to store everything. I know how to export the certificate but I
don't know how to export the private key and when I use the keytool
certificate, the server crashes with this message:

FATAL: could not load server certificate file "server.crt": no start line

Sorry about too many questions, but anyone can help me to understand more
about ssl in PostgreSQL?


--
Regards,

Jose Berardo




--
Atenciosamente,

Jose Berardo
Especializa Treinamentos
www.especializa.com.br
81 3465.0032

--0016e6db2b1c15e29804840cc0e6
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Re: PostgreSQL with SSL

Jose Berardo wrote:
> Hi,
>
> I'm new on using SSL in PostgreSQL.
>
> I've created a self-signed certificate using openssl and started the server
> with ssl suport.
>
> But I have some question, can you help me?
>
> - There is any parameter to configure the path (and name) to certificate
> (server.crt) and private key (server.key) like hba_file or ident_file in
> postgresql.conf or any other?

No, we only allow those fixed file names.

> - Is it possible to store the server.key in a ciphered file with triple-des
> and configure the PostgreSQL to use a simetric-key to open it when it's
> necessary?
> Maybe I'm wrong but my server only works with I plain private key.

No.

> I'm trying to use the java keytool in place of openssl.
> - I believe that it not possible to start the PostgreSQL server without
> openssl (and ssl-dev package in debian), is it correct?

Yes, I don't think the java keytool works.

> - When I create keys and certificates with keytool, it creates a java
> keystore to store everything. I know how to export the certificate but I
> don't know how to export the private key and when I use the keytool
> certificate, the server crashes with this message:
>
> FATAL: could not load server certificate file "server.crt": no start line
>
> Sorry about too many questions, but anyone can help me to understand more
> about ssl in PostgreSQL?

Have you read the documentation about creating a server key?

http://www.postgresql.org/docs/8.4/static/ssl-tcp.html

--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: PostgreSQL with SSL

Hello.

> > don't know how to export the private key and when I use the keytool
> > certificate, the server crashes with this message:
> >=20
> > FATAL: could not load server certificate file "server.crt": no star=
t
> line
> >=20
> > Sorry about too many questions, but anyone can help me to understand
> more
> > about ssl in PostgreSQL?
>=20
> Have you read the documentation about creating a server key?
>=20
> http://www.postgresql.org/docs/8.4/static/ssl-tcp.html

It is important that the server.crt and server.key files are in so-called=
PEM format.=20
So they have to be text files and look like:

-----BEGIN CERTIFICATE-----

-----END CERTIFICATE-----

and=20

-----BEGIN RSA PRIVATE KEY-----

-----END RSA PRIVATE KEY-----

This is the default format produced by openssl.=20
The private key format is also openssl-specific, so probably you are out =
of luck with keytool.

Regards,
Martin

--=20
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: PostgreSQL with SSL

--0016e6db2d81734a2904844d16ee
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hello.

As I have told, I'm new on using PostgreSQL over SSL connections.
I have worked with SSL before, but I'm really far from being an expert abou=
t
it.

Sorry for asking you before read all the documentation, I have looked for I=
t
in wrong pages and didn't find anything. After writing, I read a lot about
this subject because I'm a teacher, PostgreSQL enthusiastic and I've starte=
d
to write a tutorial about SSL.

Maybe my questions are the same of someone, so let me talk about them (than=
k
you Bruce for the answers):

> > - There is any parameter to configure the path (and name) to certificat=
e
> > (server.crt) and private key (server.key) like hba_file or ident_file i=
n
> > postgresql.conf or any other?
>
> No, we only allow those fixed file names.

Sorry, the documentation told me this just after my precipitated question.

> > - Is it possible to store the server.key in a ciphered file with
triple-des
> > and configure the PostgreSQL to use a simetric-key to open it when it's
> > necessary?
> > Maybe I'm wrong but my server only works with I plain private key.
>
> No.

I believe that it may be a good idea, it may bring another security level,
I'm wrong?
Just saving the private key file inside the cluster with no privilegies for
other users (the server suggests 0600 mask for it) is still sufficient to
protected the key?
I believe that may be interesting the server, at start time, when reading
the key, asks the administrator the password (e.g. a triple-des simetric
key) the same way that OpenSSL asks when we need to generate de public key
or generate crt or csr.
What do you think, Bruce?

> > I'm trying to use the java keytool in place of openssl.
> > - I believe that it not possible to start the PostgreSQL server without
> > openssl (and ssl-dev package in debian), is it correct?
>
> Yes, I don't think the java keytool works.

Oh, the documentation defeated me twice. The server reads the openssl
configuration at start time too.
The keytool may be used only to generate the key pair and the certificate,
but it can not export the private key from its keystore. You need another
tool or to write a Java code to do that.

> > - When I create keys and certificates with keytool, it creates a java
> > keystore to store everything. I know how to export the certificate but =
I
> > don't know how to export the private key and when I use the keytool
> > certificate, the server crashes with this message:
> >
> > FATAL: could not load server certificate file "server.crt": no start
line
> >
> > Sorry about too many questions, but anyone can help me to understand
more
> > about ssl in PostgreSQL?
>
> Have you read the documentation about creating a server key?
>
> http://www.postgresql.org/docs/8.4/static/ssl-tcp.html

Thank you Bruce, but this question was about the format of the certificate
generated by keytool.
I was missing the -rfc option when exporting the certificate.

Please friends, let me write an example here:

keytool -exportcert -alias myalias -file mycertificate.crt -keystore
mykeystore.jks -storepass mypass -rfc

With the -rfc, the keytool prints out the certificate in accordance with th=
e
RFC 1421.

Martin, thank you too.
The PEM format is exactly the base64 encoded format in this RFC.

Thanks a lot,

--
Jose Berardo


On Thu, Apr 15, 2010 at 4:45 AM, "Martin Münstermann" wro=
te:

> Hello.
>
> > > don't know how to export the private key and when I use the keytool
> > > certificate, the server crashes with this message:
> > >
> > > FATAL: could not load server certificate file "server.crt": no star=
t
> > line
> > >
> > > Sorry about too many questions, but anyone can help me to understand
> > more
> > > about ssl in PostgreSQL?
> >
> > Have you read the documentation about creating a server key?
> >
> > http://www.postgresql.org/docs/8.4/static/ssl-tcp.html
>
> It is important that the server.crt and server.key files are in so-called
> PEM format.
> So they have to be text files and look like:
>
> -----BEGIN CERTIFICATE-----
>
> -----END CERTIFICATE-----
>
> and
>
> -----BEGIN RSA PRIVATE KEY-----
>
> -----END RSA PRIVATE KEY-----
>
> This is the default format produced by openssl.
> The private key format is also openssl-specific, so probably you are out =
of
> luck with keytool.
>
> Regards,
> Martin
>
> --
> GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
>



--=20
Atenciosamente,

Jose Berardo
Especializa Treinamentos
www.especializa.com.br
+55 81 3465.0032

--0016e6db2d81734a2904844d16ee
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hello.

As I have told, I'm new on using PostgreSQL over SSL conn=
ections.
I have worked with SSL before, but I'm really far from bein=
g an expert about it.

Sorry for asking you before read all the docum=
entation, I have looked for It in wrong pages and didn't find anything.=
After writing, I read a lot about this subject because I'm a teacher, =
PostgreSQL enthusiastic and I've started to write a tutorial about SSL.=



Maybe my questions are the same of someone, so let me talk about them (=
thank you Bruce for the answers):

> > - There is any parameter=
to configure the path (and name) to certificate
> > (server.crt) =
and private key (server.key) like hba_file or ident_file in

> > postgresql.conf or any other?
>
> No, we only allow t=
hose fixed file names.

Sorry, the documentation told me this just af=
ter my precipitated question.

> > - Is it possible to store th=
e server.key in a ciphered=A0 file with triple-des

> > and configure the PostgreSQL to use a simetric-key to open it whe=
n it's
> > necessary?
> > Maybe I'm wrong but my =
server only works with I plain private key.
>
> No.


I believe that it may be a good idea, it may bring another security level, =
I'm wrong?
Just saving the private key file inside the cluster with =
no privilegies for other users (the server suggests 0600 mask for it) is st=
ill sufficient to protected the key?

I believe that may be interesting the server, at start time, when reading t=
he key, asks the administrator the password (e.g. a triple-des simetric key=
) the same way that OpenSSL asks when we need to generate de public key or =
generate crt or csr.

What do you think, Bruce?

> > I'm trying to use the java k=
eytool in place of openssl.
> > - I believe that it not possible t=
o start the PostgreSQL server without
> > openssl (and ssl-dev pac=
kage in debian), is it correct?

>
> Yes, I don't think the java keytool works.

Oh, the =
documentation defeated me twice. The server reads the openssl configuration=
at start time too.
The keytool may be used only to generate the key pai=
r and the certificate, but it can not export the private key from its keyst=
ore. You need another tool or to write a Java code to do that.


> > - When I create keys and certificates with keytool, it create=
s a java
> > keystore to store everything. I know how to export th=
e certificate but I
> > don't know how to export the private k=
ey and when I use the keytool

> > certificate, the server crashes with this message:
> > r>> >=A0 FATAL:=A0 could not load server certificate file "serve=
r.crt": no start line
> >
> > Sorry about too many q=
uestions, but anyone can help me to understand more

> > about ssl in PostgreSQL?
>
> Have you read the docume=
ntation about creating a server key?
>
>      =A0 <=
a href=3D"http://www.postgresql.org/docs/8.4/static/ssl-tcp.ht ml">http://ww=
w.postgresql.org/docs/8.4/static/ssl-tcp.html


Thank you Bruce, but this question was about the format of the certific=
ate generated by keytool.
I was missing the -rfc option when exporting t=
he certificate.

Please friends, let me write an example here:


keytool -exportcert -alias myalias -file mycertificate.crt -keystore my=
keystore.jks -storepass mypass -rfc

With the -rfc, the keytool print=
s out the certificate in accordance with the RFC 1421.

Martin, thank=
you too.

The PEM format is exactly the base64 encoded format in this RFC.

Tha=
nks a lot,

--
Jose Berardo

Re: PostgreSQL with SSL

Jose Berardo writes:
>>> - Is it possible to store the server.key in a ciphered file with

>> No.

> I believe that it may be a good idea, it may bring another security level,

Not really.

> Just saving the private key file inside the cluster with no privilegies for
> other users (the server suggests 0600 mask for it) is still sufficient to
> protected the key?

If someone can access that file, they can also attach to the running
server process and pull the decrypted key out of it. In any case,
providing the server with the key to decrypt the ssl key is not going
to be convenient in operation. You're not going to want to store that
key on disk are you? Do you want somebody around to manually provide
it every time the server restarts? That gets old pretty fast, when
all it's buying you is a largely-imaginary security gain.

regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: PostgreSQL with SSL

Hello.

> > > I'm trying to use the java keytool in place of openssl.
> > > - I believe that it not possible to start the PostgreSQL server
> without
> > > openssl (and ssl-dev package in debian), is it correct?
> >
> > Yes, I don't think the java keytool works.
>=20
> Oh, the documentation defeated me twice. The server reads the openssl
> configuration at start time too.
> The keytool may be used only to generate the key pair and the certifica=
te,
> but it can not export the private key from its keystore. You need anoth=
er
> tool or to write a Java code to do that.

OpenSSL has two ways to store private keys:
1. an own proprietary format
2. standard PKCS#8

The default as used in the postgresql doc is to produce the proprietary f=
ormat.=20
Don't know if PostgreSQL can handle PKCS#8 keys.

If you'd like to check, here is a command to produce PKCS#8:
openssl pkcs8 -in server.key -out server.p8 -topk8

Jose, writing a tutorial sounds promising. If I can be of any help, just =
contact me.

Martin

--=20
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: PostgreSQL with SSL

Hello,



On Thu, Apr 15, 2010 at 6:30 PM, Tom Lane wrote:
> Jose Berardo writes:
>>>> - Is it possible to store the server.key in a ciphered =A0file with
>
>>> No.
>
>> I believe that it may be a good idea, it may bring another security leve=
l,
>
> Not really.
>
>> Just saving the private key file inside the cluster with no privilegies =
for
>> other users (the server suggests 0600 mask for it) is still sufficient to
>> protected the key?
>
> If someone can access that file, they can also attach to the running
> server process and pull the decrypted key out of it. =A0In any case,
> providing the server with the key to decrypt the ssl key is not going
> to be convenient in operation. =A0You're not going to want to store that
> key on disk are you? =A0Do you want somebody around to manually provide
> it every time the server restarts? =A0That gets old pretty fast, when
> all it's buying you is a largely-imaginary security gain.
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane
>

Thanks Tom. Your few words were a very elucidative explanation.
I thought that attacking the running server process was much more
difficult than just open a file, and the needs of someone to provide
the symmetric key which will open the private key was just a question
of trade-off (security vs availability).


--=20
Regards,

Jose Berardo
Especializa Treinamentos
www.especializa.com.br
+55 81 3465.0032

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: PostgreSQL with SSL

Hello,

On Fri, Apr 16, 2010 at 4:34 AM, "Martin Münstermann" wr=
ote:
> Hello.
>
>> > > I'm trying to use the java keytool in place of openssl.
>> > > - I believe that it not possible to start the PostgreSQL server
>> without
>> > > openssl (and ssl-dev package in debian), is it correct?
>> >
>> > Yes, I don't think the java keytool works.
>>
>> Oh, the documentation defeated me twice. The server reads the openssl
>> configuration at start time too.
>> The keytool may be used only to generate the key pair and the certificat=
e,
>> but it can not export the private key from its keystore. You need another
>> tool or to write a Java code to do that.
>
> OpenSSL has two ways to store private keys:
> 1. an own proprietary format
> 2. standard PKCS#8
>
> The default as used in the postgresql doc is to produce the proprietary f=
ormat.
> Don't know if PostgreSQL can handle PKCS#8 keys.
>
> If you'd like to check, here is a command to produce PKCS#8:
> openssl pkcs8 -in server.key =A0-out server.p8 -topk8

I've test your suggestion, but it didn't work. Results bellow:

$ Enter PEM pass phrase:
FATAL: could not load private key file "server.key": problems getting pass=
word

As Tom Laine has explained, the service don't work with encrypted keys
because it would need to ask the administrator for the password (like
above), and would not start until he gives it. The server would need
to keep the clear key in memory for every connection. Anyway, would
still be possible one attacker obtain the key.

>
> Jose, writing a tutorial sounds promising. If I can be of any help, just =
contact me.

Thanks Martin, I'm writing in portuguese, but I will ask a friend for
help me in translation to english.
When it's done, I will send to you. Any consideration will be extreme welco=
me.

>
> Martin
>
> --
> GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
>



--=20
Regards,

Jose Berardo
Especializa Treinamentos
www.especializa.com.br
+55 81 3465.0032

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin